﻿using System.Data.SqlClient;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Helper.DalHelper;

namespace Helper.Tests.DalHelper
{
    [TestClass]
    public class SqlCommandExtensionTest
    {
        [TestMethod]
        public void should_add_where_clause()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Where();

            Assert.AreEqual(sqlCommand.CommandText, query + " WHERE ");
        }

        [TestMethod]
        public void should_add_and_clause()
        {
            var query = "SELECT * FROM TABLE WHERE 1=1";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.And();

            Assert.AreEqual(sqlCommand.CommandText, query + " AND ");
        }

        [TestMethod]
        public void should_add_equal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Equal("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.Equal("COLUMN2", new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 = @COLUMN1)  (COLUMN2 = @COLUMN2) ");

        }

        [TestMethod]
        public void should_not_add_duplicate_equal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Equal("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.Equal("COLUMN1", new SqlParameter("@COLUMN1", 1));

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 = @COLUMN1) ");

        }

        [TestMethod]
        public void should_add_notequal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.NotEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.NotEqual("COLUMN2", new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 <> @COLUMN1)  (COLUMN2 <> @COLUMN2) ");

        }

        [TestMethod]
        public void should_not_add_duplicate_notequal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.NotEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.NotEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 <> @COLUMN1) ");

        }

        [TestMethod]
        public void should_add_or_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Or("COLUMN1", new SqlParameter("@COLUMN1", 1), "COLUMN2", new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " ((COLUMN1 = @COLUMN1) OR (COLUMN2 = @COLUMN2)) ");

        }

        [TestMethod]
        public void should_not_add_duplicate_or_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Or("COLUMN1", new SqlParameter("@COLUMN1", 1), "COLUMN1", new SqlParameter("@COLUMN1", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 0);

            Assert.AreEqual(sqlCommand.CommandText, query);

        }

        [TestMethod]
        public void should_add_greaterthenorequal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.GreaterThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.GreaterThenOrEqual("COLUMN2", new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 >= @COLUMN1)  (COLUMN2 >= @COLUMN2) ");
        }

        [TestMethod]
        public void should_not_add_duplicate_greaterthenorequal_parameter()
        {

            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.GreaterThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.GreaterThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 >= @COLUMN1) ");

        }

        [TestMethod]
        public void should_add_lessthenorequal_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.LessThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.LessThenOrEqual("COLUMN2", new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 <= @COLUMN1)  (COLUMN2 <= @COLUMN2) ");
        }

        [TestMethod]
        public void should_not_add_duplicate_lessthenorequal_parameter()
        {

            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.LessThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            sqlCommand = sqlCommand.LessThenOrEqual("COLUMN1", new SqlParameter("@COLUMN1", 1));

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 <= @COLUMN1) ");

        }

        [TestMethod]
        public void should_add_between_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Between("COLUMN1", new SqlParameter("@COLUMN1", 1), new SqlParameter("@COLUMN2", 2));

            Assert.AreEqual(sqlCommand.Parameters.Count, 2);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 BETWEEN @COLUMN1 AND @COLUMN2) ");
        }

        [TestMethod]
        public void should_not_add_duplicate_between_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Between("COLUMN1", new SqlParameter("@COLUMN1", 1), new SqlParameter("@COLUMN1", 1));

            Assert.AreEqual(sqlCommand.Parameters.Count, 0);

            Assert.AreEqual(sqlCommand.CommandText, query);
        }

        [TestMethod]
        public void should_right_parse_parameter()
        {

            string pPerc1 = string.Empty;

            string pPerc2 = string.Empty;

            //StartWith
            SqlCommandExtension.GetLikeSymbol(LikeParams.StartWith, ref pPerc1, ref pPerc2);

            Assert.AreEqual(pPerc1, "'%' +");

            Assert.AreEqual(pPerc2, string.Empty);


            //EndWith
            pPerc1 = string.Empty;

            pPerc2 = string.Empty;

            SqlCommandExtension.GetLikeSymbol(LikeParams.EndWith, ref pPerc1, ref pPerc2);

            Assert.AreEqual(pPerc2, "+ '%'");

            Assert.AreEqual(pPerc1, string.Empty);

            //Include
            pPerc1 = string.Empty;

            pPerc2 = string.Empty;

            SqlCommandExtension.GetLikeSymbol(LikeParams.Include, ref pPerc1, ref pPerc2);

            Assert.AreEqual(pPerc1, "'%' +");

            Assert.AreEqual(pPerc2, "+ '%'");


        }

        [TestMethod]
        public void should_add_like_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Like("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 LIKE '%' + @COLUMN1 ) ");

        }

        [TestMethod]
        public void should_not_add_duplicate_like_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.Like("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            sqlCommand = sqlCommand.Like("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 LIKE '%' + @COLUMN1 ) ");
        }

        [TestMethod]
        public void should_add_notlike_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.NotLike("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 NOT LIKE '%' + @COLUMN1 ) ");

        }

        [TestMethod]
        public void should_not_add_duplicate_notlike_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.NotLike("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            sqlCommand = sqlCommand.NotLike("COLUMN1", new SqlParameter("@COLUMN1", 1), LikeParams.StartWith);

            Assert.AreEqual(sqlCommand.Parameters.Count, 1);

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 NOT LIKE '%' + @COLUMN1 ) ");
        }

        [TestMethod]
        public void should_add_in_parameter()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.In("COLUMN1", "2");

            Assert.AreEqual(sqlCommand.CommandText, query + " (COLUMN1 IN (2)) ");

        }

        [TestMethod]
        public void should_add_orderby_to_query()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.OrderBy(new[] { "COLUMN1", "COLUMN2" });

            Assert.AreEqual(sqlCommand.CommandText, query + "  ORDER BY COLUMN1,COLUMN2");
        }

        [TestMethod]
        public void should_add_groupby_to_query()
        {
            var query = "SELECT * FROM TABLE";

            var sqlCommand = new SqlCommand(query);

            sqlCommand = sqlCommand.GroupBy(new[] { "COLUMN1", "COLUMN2" });

            Assert.AreEqual(sqlCommand.CommandText, query + "  GROUP BY COLUMN1,COLUMN2");
        }
    }
}